﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;

namespace BenhVien
{
    public partial class ThongtinBN : Form
    {
        private SqlDataAdapter da = new SqlDataAdapter();
        private DataTable dt = new DataTable();
        private Connect conn = new Connect();
        public ThongtinBN()
        {
            InitializeComponent();
        }

        private void ThongtinBN_Load(object sender, EventArgs e)
        {
            conn.connect();
            getdata();
            binding();
        }
        public void getdata() 
        {
            SqlCommand command = new SqlCommand();
            command.Connection = conn.con;
            command.CommandType = CommandType.Text;
            command.CommandText = @"select * from BenhNhan";
            da.SelectCommand = command;
            da.Fill(dt);
            dataGridView1.DataSource = dt;
        }
        private void binding()
        {
            tbMBN.DataBindings.Clear();
            tbMBN.DataBindings.Add("Text", dataGridView1.DataSource, "MaBN");
            tbHBN.DataBindings.Clear();
            tbHBN.DataBindings.Add("Text", dataGridView1.DataSource, "Ho");
            tbTBN.DataBindings.Clear();
            tbTBN.DataBindings.Add("Text", dataGridView1.DataSource, "Ten");
            tbNS.DataBindings.Clear();
            tbNS.DataBindings.Add("Text", dataGridView1.DataSource, "NgaySinh");
            tbNDK.DataBindings.Clear();
            tbNDK.DataBindings.Add("Text", dataGridView1.DataSource, "NgayDangKy");
            tbDC.DataBindings.Clear();
            tbDC.DataBindings.Add("Text", dataGridView1.DataSource, "DiaChi");
            tbSDT.DataBindings.Clear();
            tbSDT.DataBindings.Add("Text", dataGridView1.DataSource, "SoDienThoai");
            tbDCNN.DataBindings.Clear();
            tbDCNN.DataBindings.Add("Text", dataGridView1.DataSource, "DiaChiNguoiThan");
            tbTNN.DataBindings.Clear();
            tbTNN.DataBindings.Add("Text", dataGridView1.DataSource, "TenNguoiThan");
            tbSDTNN.DataBindings.Clear();
            tbSDTNN.DataBindings.Add("Text", dataGridView1.DataSource, "SoDienThoaiNguoiThan");
            tbQH.DataBindings.Clear();
            tbQH.DataBindings.Add("Text", dataGridView1.DataSource, "QuanHe");
            tbTBSDP.DataBindings.Clear();
            tbTBSDP.DataBindings.Add("Text", dataGridView1.DataSource, "TenBacSiDiaPhuong");
            tbDCBSDP.DataBindings.Clear();
            tbDCBSDP.DataBindings.Add("Text", dataGridView1.DataSource, "DiaChiBSDP");
            tbSDTBSDP.DataBindings.Clear();
            tbSDTBSDP.DataBindings.Add("Text", dataGridView1.DataSource, "SoDienThoaiBSDP");
            tbPK.DataBindings.Clear();
            tbPK.DataBindings.Add("Text", dataGridView1.DataSource, "MaPhongNgoaiTru");
            cbHN.DataBindings.Clear();
            cbHN.DataBindings.Add("Text", dataGridView1.DataSource, "HonNhan");
            cbGT.DataBindings.Clear();
            cbGT.DataBindings.Add("Text", dataGridView1.DataSource, "GioiTinh");
        }

        private void bt_them_Click(object sender, EventArgs e)
        {
            if (tbDC.Text == "" | tbDCBSDP.Text == "" | tbDCNN.Text == "" | tbHBN.Text == ""|tbMBN.Text==""|tbNDK.Text==""|tbNS.Text==""|tbPK.Text==""|tbQH.Text==""|tbSDT.Text==""|tbSDTBSDP.Text==""|tbSDTNN.Text==""|tbTBN.Text==""|tbTBSDP.Text==""|tbTNN.Text==""|cbGT.Text==""|cbHN.Text=="")
                MessageBox.Show("Bạn Chưa Nhập Đủ Thông Tin", "Thông Báo", MessageBoxButtons.OK);
            else
            {
                if (check("Select MaBN from BenhNhan where MaBN =@DoiSo", tbMBN.Text)==true)
                    MessageBox.Show("Ma bi trung", "Loi", MessageBoxButtons.OK);
                else
                    {
                        DataRow row = dt.NewRow();
                        row["MaBN"] = tbMBN.Text;
                        row["Ho"] = tbHBN.Text;
                        row["Ten"] = tbTBN.Text;
                        row["DiaChi"] = tbDC.Text;
                        row["NgaySinh"] = tbNS.Text;
                        row["NgayDangKy"] = tbNDK.Text;
                        row["GioiTinh"] = cbGT.Text;
                        row["SoDienThoai"] = tbSDT.Text;
                        row["HonNhan"] = cbHN.Text;
                        row["TenNguoiThan"] = tbTNN.Text;
                        row["SoDienThoaiNguoiThan"] = tbSDTNN.Text;
                        row["QuanHe"] = tbQH.Text;
                        row["DiaChiNguoiThan"] = tbDCNN.Text;
                        row["TenBacSiDiaPhuong"] = tbTBSDP.Text;
                        row["DiaChiBSDP"] = tbDCBSDP.Text;
                        row["SoDienThoaiBSDP"] = tbSDTBSDP.Text;
                        row["MaPhongNgoaiTru"] = tbPK.Text;
                        dt.Rows.Add(row);
                        SqlCommand commandInsert = new SqlCommand();
                        commandInsert.Connection = conn.con;
                        commandInsert.CommandType = CommandType.Text;
                        commandInsert.CommandText = @"Insert BenhNhan (MaBN, Ho, Ten, DiaChi, NgaySinh, NgayDangKy, GioiTinh, SoDienThoai, HonNhan, TenNguoiThan, SoDienThoaiNguoiThan, TenBacSiDiaPhuong, DiaChiBSDP, SoDienThoaiBSDP, MaPhongNgoaiTru, QuanHe, DiaChiNguoiThan) Values 
                                                ( @MaBN, @Ho, @Ten, @DiaChi, @NgaySinh, @NgayDangKy, @GioiTinh, @SoDienThoai, @HonNhan, @TenNguoiThan, @SoDienThoaiNguoiThan, @TenBacSiDiaPhuong, @DiaChiBSDP, @SoDienThoaiBSDP, @MaPhongNgoaiTru, @QuanHe, @DiaChiNguoiThan)";
                        commandInsert.Parameters.Add("MaBN", SqlDbType.NVarChar, 50, "MaBN");
                        commandInsert.Parameters.Add("Ho", SqlDbType.NVarChar, 50, "Ho");
                        commandInsert.Parameters.Add("Ten", SqlDbType.NVarChar, 50, "Ten");
                        commandInsert.Parameters.Add("DiaChi", SqlDbType.NVarChar, 50, "DiaChi");
                        commandInsert.Parameters.Add("NgaySinh", SqlDbType.Date, 50, "NgaySinh");
                        commandInsert.Parameters.Add("NgayDangKy", SqlDbType.Date, 50, "NgayDangKy");
                        commandInsert.Parameters.Add("GioiTinh", SqlDbType.NChar, 10, "GioiTinh");
                        commandInsert.Parameters.Add("SoDienThoai", SqlDbType.NVarChar, 20, "SoDienThoai");
                        commandInsert.Parameters.Add("HonNhan", SqlDbType.Char, 10, "HonNhan");
                        commandInsert.Parameters.Add("TenNguoiThan", SqlDbType.NVarChar, 50, "TenNguoiThan");
                        commandInsert.Parameters.Add("SoDienThoaiNguoiThan", SqlDbType.NVarChar, 20, "SoDienThoaiNguoiThan");
                        commandInsert.Parameters.Add("TenBacSiDiaPhuong", SqlDbType.NVarChar, 50, "TenBacSiDiaPhuong");
                        commandInsert.Parameters.Add("DiaChiBSDP", SqlDbType.NVarChar, 50, "DiaChiBSDP");
                        commandInsert.Parameters.Add("SoDienThoaiBSDP", SqlDbType.NVarChar, 20, "SoDienThoaiBSDP");
                        commandInsert.Parameters.Add("MaPhongNgoaiTru", SqlDbType.NVarChar, 50, "MaPhongNgoaiTru");
                        commandInsert.Parameters.Add("QuanHe", SqlDbType.NVarChar, 50, "QuanHe");
                        commandInsert.Parameters.Add("DiaChiNguoiThan", SqlDbType.NVarChar, 50, "DiaChiNguoiThan");
                        da.InsertCommand = commandInsert;
                        da.Update(dt);
                        MessageBox.Show("Ban da them Thanh Cong", "chuc Mung", MessageBoxButtons.OK);
                    }
                }
          }
        private bool check(String s, String s1)
        {
            bool check1 = false;
            DataTable data = new DataTable();
            SqlCommand command = new SqlCommand();
            command.Connection = conn.con;
            command.CommandType = CommandType.Text;
            command.CommandText = s;
            command.Parameters.Add("@DoiSo", SqlDbType.NVarChar, 50).Value = s1;
            da.SelectCommand = command;
            da.Fill(data);
            if (data.Rows.Count > 0) check1 = true;
            return check1;
        }

        private void bt_Xoa_Click(object sender, EventArgs e)
        {
            DataRow row = dt.Select("MaBN like '" +tbMBN.Text+ "'")[0];
            row.BeginEdit();
            row.Delete();
            row.EndEdit();
            SqlCommand commandDelete = new SqlCommand();
            commandDelete.Connection = conn.con;
            commandDelete.CommandType = CommandType.Text;
            commandDelete.CommandText = @"Delete from BenhNhan Where MaBN=@MaBN";
            commandDelete.Parameters.Add("@MaBN", SqlDbType.NVarChar, 50, "MaBN");
            da.DeleteCommand = commandDelete;
            da.Update(dt);
            MessageBox.Show("ban da xóa thanh cong", "chuc mung", MessageBoxButtons.OK);
        }

        private void bt_NhapMoi_Click(object sender, EventArgs e)
        {
            tbMBN.Text = ""; tbDC.Text = ""; tbDCBSDP.Text = ""; tbDCNN.Text = "";
            tbHBN.Text = ""; tbNDK.Text = ""; tbNS.Text = ""; tbPK.Text = ""; tbQH.Text = "";
            tbSDT.Text = ""; tbSDTBSDP.Text = ""; tbSDTNN.Text = ""; tbTBN.Text = ""; tbTBSDP.Text = "";
            tbTNN.Text = ""; cbGT.Text = ""; cbHN.Text = "";
        }

        private void bt_Luu_Click(object sender, EventArgs e)
        {
            DataRow row = dt.Select("MaBN like '" + tbMBN.Text + "'")[0];
            row.BeginEdit();
            row["MaBN"] = tbMBN.Text;
            row["Ho"] = tbHBN.Text;
            row["Ten"] = tbTBN.Text;
            row["DiaChi"] = tbDC.Text;
            row["NgaySinh"] = tbNS.Text;
            row["NgayDangKy"] = tbNDK.Text;
            row["GioiTinh"] = cbGT.Text;
            row["SoDienThoai"] = tbSDT.Text;
            row["HonNhan"] = cbHN.Text;
            row["TenNguoiThan"] = tbTNN.Text;
            row["SoDienThoaiNguoiThan"] = tbSDTNN.Text;
            row["QuanHe"] = tbQH.Text;
            row["DiaChiNguoiThan"] = tbDCNN.Text;
            row["TenBacSiDiaPhuong"] = tbTBSDP.Text;
            row["DiaChiBSDP"] = tbDCBSDP.Text;
            row["SoDienThoaiBSDP"] = tbSDTBSDP.Text;
            row["MaPhongNgoaiTru"] = tbPK.Text;
            row.EndEdit();
            SqlCommand commandUpdate = new SqlCommand();
            commandUpdate.Connection = conn.con;
            commandUpdate.CommandType = CommandType.Text;
            commandUpdate.CommandText = @"Update BenhNhan Set Ho= @Ho,
                                                              Ten= @Ten,
                                                              NgaySinh= @NgaySinh,
                                                              NgayDangKy= @NgayDangKy,
                                                              DiaChi=@DiaChi,
                                                              SoDienThoai=@SoDienThoai,
                                                              GioiTinh=@GioiTinh,
                                                              HonNhan=@HonNhan,
                                                              TenNguoiThan=@TenNguoiThan,
                                                              DiaChiNguoiThan=@DiaChiNguoiThan,
                                                              SoDienThoaiNguoiThan=@SoDienThoaiNguoiThan,
                                                              QuanHe=@QuanHe,
                                                              TenBacSiDiaPhuong=@TenBacSiDiaPhuong,
                                                              DiaChiBSDP=@DiaChiBSDP,
                                                              SoDienThoaiBSDP=SoDienThoaiBSDP,
                                                              MaPhongNgoaiTru=@MaPhongNgoaiTru
                                                              where MaBN= @MaBN";
            commandUpdate.Parameters.Add("@MaBN", SqlDbType.NVarChar, 50, "MaBN");
            commandUpdate.Parameters.Add("@Ho", SqlDbType.NVarChar, 50, "Ho");
            commandUpdate.Parameters.Add("@Ten", SqlDbType.NVarChar, 50, "Ten");
            commandUpdate.Parameters.Add("@NgaySinh", SqlDbType.Date, 20, "NgaySinh");
            commandUpdate.Parameters.Add("@DiaChi", SqlDbType.NVarChar, 50, "DiaChi");
            commandUpdate.Parameters.Add("@NgayDangKy", SqlDbType.Date, 20, "NgayDangKy");
            commandUpdate.Parameters.Add("@SoDienThoai", SqlDbType.NVarChar, 20, "SoDienThoai");
            commandUpdate.Parameters.Add("@GioiTinh", SqlDbType.NChar, 10, "GioiTinh");
            commandUpdate.Parameters.Add("@HonNhan", SqlDbType.Char, 10, "HonNhan");
            commandUpdate.Parameters.Add("@TenNguoiThan", SqlDbType.NVarChar, 50, "TenNguoiThan");
            commandUpdate.Parameters.Add("@DiaChiNguoiThan", SqlDbType.NVarChar, 50, "DiaChiNguoiThan");
            commandUpdate.Parameters.Add("@SoDienThoaiNguoiThan", SqlDbType.NVarChar, 20, "SoDienThoaiNguoiThan");
            commandUpdate.Parameters.Add("@QuanHe", SqlDbType.NVarChar, 50, "QuanHe");
            commandUpdate.Parameters.Add("@TenBacSiDiaPhuong", SqlDbType.NVarChar, 50, "TenBacSiDiaPhuong");
            commandUpdate.Parameters.Add("@DiaChiBSDP", SqlDbType.NVarChar, 50, "DiaChiBSDP");
            commandUpdate.Parameters.Add("@SoDienThoaiBSDP", SqlDbType.NVarChar, 20, "SoDienThoaiBSDP");
            commandUpdate.Parameters.Add("@MaPhongNgoaiTru", SqlDbType.NVarChar, 50, "MaPhongNgoaiTru");
            da.UpdateCommand = commandUpdate;
            da.Update(dt);
            MessageBox.Show("ban da chinh sua thanh cong", "chuc mung", MessageBoxButtons.OK);
        }

        private void bt_Reset_Click(object sender, EventArgs e)
        {
            tbMBN.Enabled = false;
            bt_Luu.Enabled = true;
            bt_Xoa.Enabled = true;
            dt.Clear();
            getdata();
        }
    }

}
    